package edu.zju.gis.dbfg.server.util;

import edu.zju.gis.dbfg.server.model.ExternalDataArgs;
import edu.zju.gis.dbfg.server.model.vo.tableRecordsWithTotalsize;
import org.json.simple.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import sun.net.www.http.HttpClient;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public final class ExternalDataHelper {
    private static Connection pgConnection = null;
    private static Connection mysqlConnection = null;
    private static HttpClient apiConnection;

    //测试连接
    public static void getConnection(ExternalDataArgs sourceConnect) throws Exception {
        switch (sourceConnect.getType()) {
            case "postgresql":
//                try {
                    Class.forName("org.postgresql.Driver");
                    String url = "jdbc:postgresql://" + sourceConnect.getIp() + ":" + sourceConnect.getPort() + "/" + sourceConnect.getDatabaseName();
                    pgConnection = DriverManager.getConnection(url,
                            sourceConnect.getUserName(), sourceConnect.getPassword());
//                } catch (Exception e) {
//                    e.printStackTrace();
//                    System.err.println(e.getClass().getName() + ": " + e.getMessage());
//                }
                System.out.println("Opened database successfully");
//                return pgConnection;
            case "mysql":
//                return mysqlConnection;

            default:
//                return apiConnection;

        }
    }

    //获取连接数据库中的所有表和、模式及字段
    public static List<JSONObject> queryConstruct(ExternalDataArgs sourceConnect) throws Exception {
        List<JSONObject> resObject = new ArrayList<>();
        switch (sourceConnect.getType()) {
            case "postgresql":
                if (pgConnection==null || pgConnection.isClosed()){
                    getConnection(sourceConnect);
                }
                Statement stmt = pgConnection.createStatement();
                if (sourceConnect.getSchemaName() == null) {
                    //获取schema列表
                    String selectScheSql = "select * from information_schema.schemata";
                    resObject = querySql(stmt, selectScheSql, "schema_name");
                } else if (sourceConnect.getTableName() == null) {
                    //获取表名列表
                    String selectSql = "select * from pg_tables where schemaname ='" + sourceConnect.getSchemaName() + "'";
                    resObject = querySql(stmt, selectSql, "tablename");
                } else if (sourceConnect.getFields() == null) {
                    //获取某个表的所有字段
//                    select * from information_schema.columns
//                    where table_schema='public' and table_name='tb_test1';
                    String sqlfields = "select * from information_schema.columns   where table_schema='" + sourceConnect.getSchemaName() + "' and table_name='" + sourceConnect.getTableName() + "'";
                    resObject = querySql(stmt, sqlfields, "column_name");
                }
                return resObject;
            case "mysql":
                return resObject;
            default:
                return resObject;
        }
    }

    private static List<JSONObject> querySql(Statement stmt, String sql, String columnName) throws Exception {
        List<JSONObject> resObject = new ArrayList<>();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("value", rs.getString(columnName));
            jsonObject.put("label", rs.getString(columnName));
            resObject.add(jsonObject);
        }
        rs.close();
        stmt.close();
        return resObject;
    }


    public static tableRecordsWithTotalsize pageSearh(ExternalDataArgs sourceConnect,int pageNo,int pageSize) throws Exception {
        tableRecordsWithTotalsize tableRecordsWithTotalsize = new tableRecordsWithTotalsize();
        switch (sourceConnect.getType()) {
            case "postgresql":
                if (pgConnection==null || pgConnection.isClosed()) {
                    getConnection(sourceConnect);
                }
                Statement stmt = pgConnection.createStatement();
                //获取字段
                List<JSONObject> jsonObjects = queryConstruct(sourceConnect);
                String colName = jsonObjects.get(0).get("value").toString();
                //从第3条开始，取10条
//                select * from testwmx order by name desc limit 10 offset 3
                int offset=(pageNo-1)*pageSize;
                String sql = "select * from " + sourceConnect.getTableName()+" order by "+colName+" desc limit "+pageSize+" offset "+ offset;
                String sqlAll="select * from " + sourceConnect.getTableName();
                tableRecordsWithTotalsize = search(stmt, sql,sqlAll);
                return tableRecordsWithTotalsize;
            case "mysql":
                return tableRecordsWithTotalsize;
            default:
                return tableRecordsWithTotalsize;
        }
    }

    private static tableRecordsWithTotalsize search(Statement stmt, String sql,String sqlAll) throws Exception {
//     String sql1 = "select * from " + tableName;
        ResultSet rs = stmt.executeQuery(sql);
        tableRecordsWithTotalsize res = new tableRecordsWithTotalsize();
        //获取列名
        ResultSetMetaData rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();
        String[] name = new String[count];
        int[] type = new int[count];
        for (int i = 0; i < count; i++) {
            name[i] = rsmd.getColumnName(i + 1);
            type[i] = rsmd.getColumnType(i + 1);
        }
        List<JSONObject> records = new ArrayList<>();

        while (rs.next()) {
            JSONObject jsonObject = new JSONObject();
            for (int i = 0; i < count; i++) {
                String key = name[i];
                //todo 根据type判断用那个函数得到值getString 或getLong等
                String value = rs.getString(key);
                jsonObject.put(key, value);
            }
            records.add(jsonObject);
        }
        res.setRecords(records);

        ResultSet rsAll=stmt.executeQuery(sqlAll);
        int rowCount = 0;
        while (rsAll.next()) {
            rowCount++;
        }
        res.setTotalSize(rowCount);

        rs.close();
        stmt.close();
        return res;
    }

    public static boolean close(){
        try {
            if(pgConnection!=null){
                pgConnection.close();
            }
            if(mysqlConnection!=null){
                mysqlConnection.close();
            }
            if(apiConnection!=null){
//                apiConnection.close();
            }
            return true;
        }catch (Exception e){
            System.out.println(e.getMessage());
            return false;
        }
    }
}
